package com.example.tool.task;


import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileInputStream;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @Author lz
 * @Date 2022/2/16 15:40
 */
@Slf4j
public class ExcelUtil {
    private static final String EXCEL2003 = "xls";
    private static final String EXCEL2007 = "xlsx";

    /**
     * @description:  解析excel表格--直接通过数据行来解析
     * @param: filePath --需要解析的表格所在路径
     * @param titleRowStart --多行标题起始行
     * @param titleRowend --多行标题结束行，若为单行标题，则和起始行一样
     * @param dataRow --数据开始行数
     * @param limsMap --映照表格中需要上传到lims的字段信息
     * @return:
     * @author lhy
     * @date: 2021-08-26 14:47
     */
    public static List<Map<String,String>> parseExcel(FileInputStream inputStream, String fileName,int titleRowStart, int titleRowend, int dataRow, Map<String, Integer> limsMap) {
        List<Map<String,String>> list = new ArrayList<>();
        Workbook workbook;
//        String fileName = multipartFile.getOriginalFilename();
        boolean isSuccess;
//        FileInputStream inputStream = null;
        try{

//            inputStream = (FileInputStream) multipartFile.getInputStream();
            //后缀为.xlsx的为2007以上版本，用XSSFWorkbook
            if (fileName.endsWith(EXCEL2007)) {
                workbook = new XSSFWorkbook(inputStream);
            } else if (fileName.endsWith(EXCEL2003)) {
                workbook = new HSSFWorkbook(inputStream);
            } else {
                log.error("暂无符合条件的文件-----has not eligible file");
                workbook = null;
                return null;
            }
            isSuccess = parseToList(workbook,titleRowStart,titleRowend,dataRow,limsMap,list,fileName);
        } catch (Exception e) {
            log.error("获取文件失败,文件名为" + fileName,e);
            return null;
        } finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (Exception e) {
                    log.error("关闭流失败");
                }
            }
        }

        return list;
    }


//
    private static boolean parseToList(Workbook workbook, int titleRowStart, int titleRowend, Integer dataRow, Map<String,Integer> limsMap, List<Map<String,String>> resultList, String fileName) {
        if (workbook == null) {
            return false;
        }
        Sheet sheet = workbook.getSheetAt(0);
        Map<Integer,String> newMap = new HashMap<>();
        //根据传递过来的LIMS_MAP解析出所需的列对应的标题名和标题数
        for (int i = titleRowStart; i <= titleRowend; i++) {
//            org.apache.poi.ss.usermodel.Row row = sheet.getRow(i);
            Row row = sheet.getRow(i);
            for (int j = 0; j <  row.getLastCellNum(); j++) {
//                org.apache.poi.ss.usermodel.Cell cell = row.getCell(j);
                Cell cell = row.getCell(j);
                if (cell == null || StringUtils.isEmpty(cell.getStringCellValue())) {
                    continue;
                }
                String cellValue = cell.getStringCellValue();
                //用于去除字符尾部的空白字符。
                String value = " ".equals(cellValue.substring(cellValue.length()-1))?cellValue.substring(0,cellValue.length()-1):cellValue;
                if (limsMap.containsKey(value)) {
                    int columnIndex = cell.getColumnIndex();
                    newMap.put(cell.getColumnIndex() + limsMap.get(value),value);
                }
            }
        }
        for (int i = dataRow; i <=sheet.getLastRowNum(); i++) {
            Map<String,String> map = new HashMap<>();
            Row row = sheet.getRow(i);
            if (row == null || row.getLastCellNum() < 1) {
                continue;
            }
            for (Map.Entry<Integer,String> entry : newMap.entrySet()) {
                Cell cell = row.getCell(entry.getKey());
                if (cell == null) {
                    continue;
                }
                String text = "";
                try {
                    if (cell.getCellTypeEnum() == CellType.BLANK) {//空值
                        continue;
                    }else if (cell.getCellTypeEnum() == CellType.FORMULA) {//公式
                        text = cell.getStringCellValue();
                    }else if (cell.getCellTypeEnum() == CellType.NUMERIC){//数字
                        //判断是否为时间格式
                        if (DateUtil.isCellDateFormatted(cell)) {
                            Date time = cell.getDateCellValue();
                            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
                            text = simpleDateFormat.format(time);
                        }else {
                            Double d = cell.getNumericCellValue();
                            text = String.format("%.2f", d);
                        }
                    }else {
                        text = cell.getStringCellValue();
                    }
                }catch (Exception e) {
                    log.error("判断表格类型错误");
                    continue;
                }
                map.put(entry.getValue(),text);
            }
            if (map == null || map.size() == 0) {
                continue;
            }
            map.put("remark",fileName);
            resultList.add(map);
        }
        return true;
    }





}
